<?php
/**
 * 这个文件是Reports静态类文件的copy文件作了修改，是为财务做包含取消订单的下载
 * @author 
 * @version 
 */
class  Downloading {
/*
*筛选结果的方法
*/
	public static function reduct_detail_data($totles)
	{
		if(!empty($totles))
		{
			foreach($totles as $key=>&$value)
			{
				//if($value['status']==90){
					//$value['quantity'] = $value['ware_quantity'];
				//$value['total']=$value['no_tax_money']+$value['transportation_expense'];
				//}
				//$value['no_recieve'] = $value['quantity'] - $value['ware_quantity'];
				if($value['tax_way']==0)
				{
					//$value['tax'] = 0;
					//$value['cost']=$value['money'];
					$value['tax_way']="不计税"; 
				}else if($value['tax_way']==-1)
				{
					//$value['tax'] = $value['tax_rate']*$value['money'];
					//$value['cost']=$value['money']*(1-$value['tax_rate']);
					$value['tax_way']="内含税";
				}else{
					//$value['tax'] = $value['tax_rate']*$value['money'];
					//$value['cost']=$value['money']*(1-$value['tax_rate']);
					$value['tax_way']="外加税"; 
				}
				$value['supplier'] = Purchase::select_s_name($value['supplier']);
				$value['purchase_id'] = Erpuser::nick_by_id($value['purchase_id']);
				//if($value['status']==90){
				// $totles[$key]['no_recieve'] = 0; 
				//}else 
				//{
					if($value['quantity']-$value['ware_quantity']>=0)
										$totles[$key]['no_recieve'] = $value['quantity']-$value['ware_quantity'];
					else 
										$totles[$key]['no_recieve'] = 0;//$ts['recieve_quantity']-$ts['ware_quantity'];
				//}
				if($value['quantity']-$value['ware_quantity']<0)
					$totles[$key]['extra_recieve_quantity'] = $value['ware_quantity'] - $value['quantity'];
				else
					$totles[$key]['extra_recieve_quantity'] = 0;

				$status = basic::get_podetail_status();
				$value['status'] = ($status[$value['status']])?$status[$value['status']]:'';

				$pay_way = basic::get_pay_way();
				$value['pay_way'] = ($pay_way[$value['pay_way']])?$pay_way[$value['pay_way']]:'';
				
				$pay_time ='';
				$pays = ORM::factory('purchaseorder_pay')->where('purchaseorder_id','=',$value['poid'])->find_all()->as_array();
				foreach($pays as $pay){
					$pay_time .=date_format(date_create($pay->pay_date),'Y-m-d').',';
				}
				$value['pay_time'] =$pay_time;
			}
		}
		return $totles;
	}
/*
*采购订单详细报表下载表格方法
*/
	public static function select_totals($condition)	
	{
		$sql = DB::select(array("purchaseorders.id",'poid'),array("purchaseorders.status",'st'),array("purchaseorders.transportation_expense",'te'),'purchaseorders.*',"purchaseorder_details.*")->from('purchaseorders');
		$sql->join('purchaseorder_details')->on('purchaseorders.id', '=', 'purchaseorder_details.purchaseorder_id');
		if(isset($condition['date_from']))
		{
			$sql->where('purchaseorder_date','>=',$condition['date_from']);			
		}
		if($condition['date_to'])
		{
			$sql->where('purchaseorder_date','<=',$condition['date_to']);
		}else{
			$sql->where('purchaseorder_date','<=',date('Y-m-d H:i:s',time()));
		}
		if($condition['supplier'])
		{
            //START     ----------------------------------CWY更新代码-------------------------------------
            $name = trim($condition['supplier']);
            $supplier = Newerpclientapi::getSuppliers(array('id'),array('name' => "like '%$name%'"));
//            $supplier = DB::select('id')
//												->from('erp_provider')
//												->where('vProviderName','like',"%".trim($condition['supplier']."%"))
//												->execute('readonlyerp')->as_array();
            //END     ----------------------------------CWY更新代码-------------------------------------
			if(!empty($supplier))
			{
				$sql->where('supplier_id','IN',$supplier);
			}
			else
				$sql->where('supplier_id','=',-1);	
			
		}
		if($condition['purchaser'])
		{
			$id = ORM::factory('user')->where('nick','=',$condition['purchaser'])->find()->id;
			if($id)
				$sql->where('purchase_id','=',$id);
			else
				$sql->where('purchase_id','=',-1);
		}
		if($condition['sku'])
		{
			$sql->where('sku','=',$condition['sku']);
		}

		if($condition['paydate_from']!='' || $condition['paydate_to']!=''){
		//通过付款时间查询
		$pay_array =array(0);
		$po_pays_sql = "SELECT `purchaseorder_id` FROM `purchaseorder_pays` where 1 ";
		if($condition['paydate_from']!=''){
			$po_pays_sql .=" AND pay_date>='".$condition['paydate_from']."'";
		}
		if($condition['paydate_to']!=''){
			$po_pays_sql .=" AND pay_date<='".$condition['paydate_to']."'";
		}
			$po_pays_sql .=" group by purchaseorder_id";
			$po_pays = DB::query(Database::SELECT,$po_pays_sql)->execute('newerp')->as_array();
		foreach($po_pays as $po_pay){
			$pay_array[] =$po_pay['purchaseorder_id'];
		}
		$sql->where('purchaseorders.id','IN',$pay_array);
		}	

		$totles      = $sql->execute('newerp')->as_array();
		$totles      = self::reduct_detail_data($totles);

		return $totles;
	}
/*
*采购订单报表的搜索的sql
*/
	public static function select_totals_purchase($condition)
	{
		$sql = self::sql_select($condition['way']);
		if($condition['date_from'])
			$sql .=" AND a.purchaseorder_date >= '".$condition['date_from']."'";
		if($condition['date_to'])
			$sql .=" AND a.purchaseorder_date <= '".$condition['date_to']."'";
		else
			$sql .=" AND a.purchaseorder_date <= '".date('Y-m-d H:i:s',time())."'";
		if($condition['supplier'])
		{
            //START     ----------------------------------CWY更新代码-------------------------------------
            $name = trim($condition['supplier']);
            $supplier = Newerpclientapi::getSuppliers(array('id'),array('name' => "like '%$name%'"));
//			$supplier = DB::select('id')
//												->from('erp_provider')
//												->where('vProviderName','like',"%".$condition['supplier']."%")
//												->execute('readonlyerp')->as_array();
            //END     ----------------------------------CWY更新代码--------------------------------------
			if(!empty($supplier))
			{
				$str = "(";$ids="";
				foreach($supplier as $value)
				{
					$ids .=$value['id']." ,";
				}
				$str .= substr($ids,0,strlen($ids)-1).")"; 
				$sql .=" AND `supplier_id` IN ".$str;
			}
			else
				$sql .=" AND supplier_id = -1";//->where('supplier_id','=',-1);
		}
		if($condition['purchaser'])
		{
		 if(empty(ORM::factory('user')->where('nick','=',$condition['purchaser'])->find()->id))
			 $user_id=-1;
		 else
		 	 $user_id=ORM::factory('user')->where('nick','=',$condition['purchaser'])->find()->id;	
		 $sql .= " AND purchase_id = '".$user_id."'";
			
		}


			if($condition['paydate_from']!='' || $condition['paydate_to']!=''){
		//通过付款时间查询
		$pay_array =array(0);
		$po_pays_sql = "SELECT `purchaseorder_id` FROM `purchaseorder_pays` where 1 ";
		if($condition['paydate_from']!=''){
			$po_pays_sql .=" AND pay_date>='".$condition['paydate_from']."'";
		}
		if($condition['paydate_to']!=''){
			$po_pays_sql .=" AND pay_date<='".$condition['paydate_to']."'";
		}
			$po_pays_sql .=" group by purchaseorder_id";
			$po_pays = DB::query(Database::SELECT,$po_pays_sql)->execute('newerp')->as_array();
		foreach($po_pays as $po_pay){
			$pay_array[] =$po_pay['purchaseorder_id'];
		}
		$sql .= "AND purchaseorders.id IN ".$pay_array;
		}	

		if($condition['sku'])
		 $sql .= " AND b.sku = '".$condition['sku']."' ";
		if($condition['way']==1)
			$sql .= " GROUP BY b.sku ORDER BY b.money DESC";
		else if($condition['way']==2)
			$sql .= " GROUP BY a.purchase_id 
							ORDER BY a.money DESC";
		else if($condition['way']==3)
			$sql .= "GROUP BY b.supplier 
							ORDER BY b.money DESC";
		else
			$sql .="GROUP BY b.sku,b.supplier 
							ORDER BY b.money DESC";

		return $sql ;
	}
/*
*采购订单报表的搜索的sql
*/ 
	public static function sql_select($way)
	{
		if($way==1)
			$sql = "SELECT b.sku,b.sku_name,b.sku_standard,b.storage_position,sum(b.quantity),b.single_price,sum(b.money),
							sum(b.transportation_expense),sum(b.recieve_quantity),sum(b.check_quantity),sum(b.ware_quantity),sum(b.no_tax_money),sum(b.tax_money) 
							FROM purchaseorders AS a,purchaseorder_details AS b 
							WHERE a.id=b.purchaseorder_id";
		else if($way==2)
		{
			$sql ="SELECT a.purchase_id,sum(b.quantity),sum(b.money),sum(b.transportation_expense),sum(b.no_tax_money),sum(b.tax_money)  
							FROM purchaseorders AS 
							a,purchaseorder_details AS b 
							WHERE a.id=b.purchaseorder_id 
							";
		}else if($way==3)
		{
			$sql ="SELECT b.supplier,sum(b.quantity),sum(b.money),sum(b.transportation_expense),sum(b.recieve_quantity),
							sum(b.check_quantity),sum(b.ware_quantity),sum(b.no_tax_money),sum(b.tax_money) 
							FROM purchaseorders AS a,purchaseorder_details AS b 
							WHERE a.id=b.purchaseorder_id
							";
		}else
		{
			$sql ="SELECT b.sku,b.sku_name,b.sku_standard,b.storage_position,b.supplier,sum(b.quantity),b.single_price,
							sum(b.money),sum(b.transportation_expense),sum(b.recieve_quantity),sum(b.check_quantity),
							sum(b.ware_quantity),sum(b.no_tax_money),sum(b.tax_money) 
							FROM purchaseorders AS a,purchaseorder_details AS b 
							WHERE a.id=b.purchaseorder_id
							";
		}
		return $sql ;
	}
/*
*采购订单报表的搜索的sql执行结果
*/ 
	public static function search_total($sql)
	{
		$datas = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
		return $datas;
	}
/*
*采购订单报表的搜索的筛选结果
*/ 
	public static function reset_sku_data($totles,$way)
	{
		if($way==1)
		{		
			foreach($totles as $key=>&$value)
			{
				if($value['sum(b.quantity)']-$value['sum(b.ware_quantity)']>=0)
										$totles[$key]['no_recieve'] = $value['sum(b.quantity)']-$value['sum(b.ware_quantity)'];
				else 
										$totles[$key]['no_recieve'] = 0;//$ts['recieve_quantity']-$ts['ware_quantity'];
				if($value['sum(b.quantity)']-$value['sum(b.ware_quantity)']<0)
					$totles[$key]['extra_recieve_quantity'] = $value['sum(b.ware_quantity)'] - $value['sum(b.quantity)'];
				else
					$totles[$key]['extra_recieve_quantity'] = 0;
			}
		}else if($way==2)
		{
			foreach($totles as $key=>&$value)
			{
				$value['purchase_id'] = Erpuser::nick_by_id($value['purchase_id']);
			}
		}else if($way==3)
		{
			foreach($totles as $key=>&$value)
			{
				if($value['sum(b.quantity)']-$value['sum(b.ware_quantity)']>=0)
										$totles[$key]['no_recieve'] = $value['sum(b.quantity)']-$value['sum(b.ware_quantity)'];
				else 
										$totles[$key]['no_recieve'] = 0;//$ts['recieve_quantity']-$ts['ware_quantity'];
				if($value['sum(b.quantity)']-$value['sum(b.ware_quantity)']<0)
					$totles[$key]['extra_recieve_quantity'] = $value['sum(b.ware_quantity)'] - $value['sum(b.quantity)'];
				else
					$totles[$key]['extra_recieve_quantity'] = 0;
				$value['supplier'] = Purchase::select_s_name($value['supplier']);
			}
		}else
		{
			foreach($totles as $key=>&$value)
			{
				if($value['sum(b.quantity)']-$value['sum(b.ware_quantity)']>=0)
										$totles[$key]['no_recieve'] = $value['sum(b.quantity)']-$value['sum(b.ware_quantity)'];
				else 
										$totles[$key]['no_recieve'] = 0;//$ts['recieve_quantity']-$ts['ware_quantity'];
				if($value['sum(b.quantity)']-$value['sum(b.ware_quantity)']<0)
					$totles[$key]['extra_recieve_quantity'] = $value['sum(b.ware_quantity)'] - $value['sum(b.quantity)'];
				else
					$totles[$key]['extra_recieve_quantity'] = 0;
				$value['supplier'] = Purchase::select_s_name($value['supplier']);
			}
		}
		return $totles;
	}
}
